package jackey.tool.sql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

/**
 * 
 * @author ZQKEY
 * 
 */
public class DBConnection {
	private DBConnectionManager connMgr = null;

	private Connection conn = null;

	private PreparedStatement prepStmt = null;

	private ResultSet rs = null;

	public CallableStatement cstmt;

	public String poolName = "";

	/**
	 * 执行SQL
	 * @param poolName
	 * @param sql
	 * @throws SQLException
	 */
	public DBConnection(String poolName, String sql) throws SQLException {
		this.poolName = poolName;
		connMgr = DBConnectionManager.getInstance();
		this.conn = connMgr.getConnection(poolName);
		prepStmt = conn.prepareStatement(sql);
	}

	/**
	 * 执行存储过程
	 * 
	 * @param poolName
	 * @throws SQLException
	 */
	public DBConnection(String poolName) throws SQLException {
		this.poolName = poolName;
		connMgr = DBConnectionManager.getInstance();
		this.conn = connMgr.getConnection(poolName);
	}

	public Connection getConnection() {
		return connMgr.getConnection(poolName);
	}

	public PreparedStatement getPreparedStatement() {
		return prepStmt;
	}

	/**
	 * 用于产生单个结果集的语句，例如 SELECT 语句。
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery() throws SQLException {
		return this.prepStmt.executeQuery();
	}

	/**
	 * 用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。
	 * @return 
	 * @throws SQLException
	 */
	public int executeUpdate() throws SQLException {
		return this.prepStmt.executeUpdate();
	}

	/**
	 * 用于执行返回多个结果集、多个更新计数或二者组合的语句。
	 * @return
	 * @throws SQLException
	 */
	public boolean execute() throws SQLException {
		return this.prepStmt.execute();
	}

	/**
	 * 有返回值的存储过程执行
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQueryProcedure(String sql, Object[] params)
			throws SQLException {
		int index = 0;
		cstmt = conn.prepareCall(sql);
		for (Object obj : params) {
			index++;
			cstmt.setObject(index, obj);
		}
		// cstmt.registerOutParameter(3, Types.INTEGER);
		rs = cstmt.executeQuery();

		return rs;
	}

	/**
	 * 返回boolean的存储过程
	 * 
	 * @param procName
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public boolean executeProcedure(String procName, Object[] params)
			throws SQLException {
		boolean flag = false;
		int index = 0;
		cstmt = conn.prepareCall(procName);
		for (Object obj : params) {
			index++;
			cstmt.setObject(index, obj == null ? "" : obj);
		}
		flag = cstmt.execute();
		return flag;
	}

	public void commit() {
		try {
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void rollback() {
		try {
			conn.rollback();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 释放连接
	 * 
	 */
	public void free() {
		try {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}
			if (this.prepStmt != null) {
				this.prepStmt.close();
				this.prepStmt = null;
			}

			if(!conn.getAutoCommit()) {
				this.commit();
				this.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 将用过的连接再回收到池中,在回收连接前,要保证上一连接操作完
		connMgr.freeConnection(poolName, this.conn);
	}
}
